#! /bin/env python3
# coding: utf-8

import os

import pandas as pd
import xlrd
import xlwt
from xlutils.copy import copy


def default_style():
    header_style = xlwt.easyxf(
        'font:name SimSun,bold on;align: vert centre, horiz center;pattern: pattern solid, fore_colour grey25;borders: top 1, bottom 1, left 1, right 1;')
    body_style = xlwt.easyxf(
        'font:name SimSun;align: vert centre, horiz center,wrap on;borders: top 1, bottom 1, left 1, right 1;')
    return {"header": header_style, "body": body_style}


def add_data_to_excel(sheet, tab_header, tab_content, width=256 * 20):
    style = default_style()
    i = 0
    for header in tab_header:
        sheet.col(tab_header.index(header)).width = width
        sheet.write(0, i, header, style['header'])
        i += 1
    for row in range(1, len(tab_content) + 1):
        for col in range(0, len(tab_content[row - 1])):
            sheet.write(row, col, tab_content[row - 1][col], style['body'])
            col += 1
        row += 1


def write_excel(xls_path, sheet_name, tab_header, tab_content):
    if all([xls_path, sheet_name, tab_header, tab_content]):
        if not os.path.exists(xls_path):
            book = xlwt.Workbook()
            sheet = book.add_sheet(sheet_name, cell_overwrite_ok=True)
            add_data_to_excel(sheet, tab_header, tab_content)
            book.save(xls_path)
        elif os.path.exists(xls_path):
            book = xlrd.open_workbook(xls_path, formatting_info=True)
            book_update = copy(book)
            sheet = book_update.add_sheet(sheet_name, cell_overwrite_ok=True)
            add_data_to_excel(sheet, tab_header, tab_content)
            # sheet name must be alphabet
            if sheet_name.isalpha():
                index = book_update.sheet_index(sheet_name)
                book_update.set_active_sheet(index)
            book_update.save(xls_path)
    else:
        print("err：excel parameters is error")
        exit()


def pd_write_excel(file_path, sheet, header, data):
    excel_file = pd.ExcelWriter(file_path)
    if isinstance(data, tuple):
        df = pd.DataFrame.from_records(list(data))
    else:
        df = pd.DataFrame.from_records(data)
    df.to_excel(excel_file, index=False, header=header, sheet_name=sheet)
    excel_file.save()


def pd_style_example():
    # https://blog.csdn.net/junhongzhang/article/details/89702340?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-6.edu_weight&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-6.edu_weight
    pass


if __name__ == "__main__":
    xls_path = r"d:\test.xls"
    csv_path = r'd:\test2.csv'
    sheet_name1 = u"出入场&订单正常"
    sheet_name2 = u"test"
    tab_header = ['subsystem_code', 'subsystem_name', 'park_in', 'park_out', 'order_num']

    tab_content1 = (('00000013', '嘉洲广场', 11039, 13109, 1865),)
    tab_content2 = (('00000013', '嘉洲广场', 11039, 13109, 1865),)
    # tab_content = '00000013123sfsafsffsdf'
    # write_excel(xls_path, sheet_name, tab_header, tab_content)

    sheet_name = [sheet_name1, sheet_name2, ]
    tab_content = [tab_content1, tab_content2]
    for sheet in sheet_name:
        write_excel(xls_path, sheet, tab_header, tab_content[sheet_name.index(sheet)])
